﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//using System.Data.OleDb;
using System.Data;
using System.Data.SQLite;

namespace BS_2
{
    class DataAccess
    {
        public static string ConnStr = @"data source = D:\\Sqlite\\sqlite_test02.db";
        //打开数据库链接
        public static SQLiteConnection Open_Conn(string ConnStr)
        {
            SQLiteConnection Conn = new SQLiteConnection(ConnStr);
            Conn.Open();
            return Conn;
        }
        //关闭数据库链接
        public static void Close_Conn(SQLiteConnection Conn)
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }
        //运行OleDb语句
        public static int Run_SQL(string SQL)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            SQLiteCommand Cmd = Create_Cmd(SQL, Conn);
            try
            {
                int result_count = Cmd.ExecuteNonQuery();
                Close_Conn(Conn);
                return result_count;
            }
            catch
            {
                Close_Conn(Conn);
                return 0;
            }
        }

        public static int Run_SQLParameter(string SQL, SQLiteParameter[] parameters)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            SQLiteCommand Cmd = Create_Cmd(SQL, Conn);
            Cmd.Parameters.Clear();
            Cmd.Parameters.AddRange(parameters);
            Cmd.CommandType = CommandType.Text;
            try
            {
                int result_count = Cmd.ExecuteNonQuery();
                Close_Conn(Conn);
                return result_count;
            }
            catch (Exception e)
            {
                Close_Conn(Conn);
                return 0;
            }
        }

        // 生成Command对象 
        public static SQLiteCommand Create_Cmd(string SQL, SQLiteConnection Conn)
        {
            SQLiteCommand Cmd = new SQLiteCommand(SQL, Conn);
            return Cmd;
        }
        // 运行OleDb语句返回 DataTable
        public static DataTable Get_DataTable(string SQL, string Table_name)
        {
            SQLiteDataAdapter Da = Get_Adapter(SQL);
            DataTable dt = new DataTable(Table_name);//DataTable(string tableName)  用指定的表名初始化DataTable 类的新实例
            Da.Fill(dt);
            return dt;
        }
        // 运行OleDb语句返回 OleDbDataReader对象
        public static SQLiteDataReader Get_Reader(string SQL)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            SQLiteCommand Cmd = Create_Cmd(SQL, Conn);
            SQLiteDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
            {
                throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return Dr;
        }
        // 运行OleDb语句返回 OleDbDataAdapter对象 
        public static SQLiteDataAdapter Get_Adapter(string SQL)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            SQLiteDataAdapter Da = new SQLiteDataAdapter(SQL, Conn);
            return Da;
        }
        // 运行OleDb语句,返回DataSet对象
        public static DataSet Get_DataSet(string SQL, DataSet Ds)
        {
            SQLiteDataAdapter Da = Get_Adapter(SQL);
            try
            {
                Da.Fill(Ds);
            }
            catch (Exception Err)
            {
                throw Err;
            }
            return Ds;
        }
        // 运行OleDb语句,返回DataSet对象
        public static DataSet Get_DataSet(string SQL, DataSet Ds, string tablename)
        {
            SQLiteDataAdapter Da = Get_Adapter(SQL);
            try
            {
                Da.Fill(Ds, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        // 运行OleDb语句,返回DataSet对象，将数据进行了分页
        public static DataSet Get_DataSet(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            SQLiteDataAdapter Da = Get_Adapter(SQL);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            Close_Conn(Conn);
            return Ds;
        }

        // 返回OleDb语句执行结果的第一行第一列
        public static string Get_Row1_Col1_Value(string SQL)
        {
            SQLiteConnection Conn = Open_Conn(ConnStr);
            string result;
            SQLiteDataReader Dr;
            try
            {
                Dr = Create_Cmd(SQL, Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr[0].ToString();
                    Dr.Close();
                }
                else
                {
                    result = "";
                    Dr.Close();
                }
            }
            catch
            {
                throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return result;
        }
    }
}
